3日目: RDBMS・MySQL

今日の流れ

データベースの概論

関係データベース・SQL・MySQL

~この辺りで休憩予定~

トランザクション・テーブル設計・パフォーマンス

課題

データベース概論

最初にちょっとアンケートコーナー

データを保存するプログラムを書いたことがありますか?

ファイル (CSV, JSON)、SQL使わないやつ(RedisとかMongoDBとか)、Firebase Realtime Databaseとか

SQLを書いたことありますか?

プログラミング言語から使ったことがありますか?

データベースとは

データ(data) = コンピュータが取り扱う情報

データベース(database) = データを集めて取り扱いやすくしたもの

Webサービスとデータベース

Webサービスは大量のデータを持つ

例えば、はてなブログならブログのデータ・記事のデータ・カテゴリーのデータ……

アクセスが増えれば増えるほど、またユーザ数が増えるほどデータの書き込み/読み込みが増える

それらを捌きながらサービスは24時間365日運用したい

データが消えてしまってはならない

そのためにデータベースを管理するための専用の仕組みを使う

ファイルでも単純なデータ管理は可能

CSVやJSONなどでテーブル構造や複雑なオブジェクトの管理を行える

code:user.csv

user_id, user_name, birthday

1, hatena-taro, 2003-03-01

2, hatena-hanako, 2005-08-01

code:user.json

{

users: [

{

id: 1,

name: "hatena-taro"

birthday: "2003-03-01"

},

{

id: 2,

name: "hatena-hanako"

birthday: "2005-08-01"

}

]

}

しかし、実際にWebアプリケーションで利用することを考えてみると……?

データ取得の際のパフォーマンスは?

並列アクセスやデータ損失にはどう対応する?

データの整合性は?

それらの解決手段を用意できたとして、自分でそれらを実装するのか?

etc...

なぜDBMSが必要なのか?

データを抽象化し、効率よく保存できる

並列アクセスができる

データ損失を防ぐ

データを抽象化し、効率よく保存できる

データを使う人はデータがどのように格納されているか意識しなくても良い

用途にあわせて最適な構造でデータを保存することができる

並列アクセスができる

同時に書き込み/読み込みが発生した時に齟齬が起きないようにできる

トランザクション/ロック機構

データ損失を防ぐ

停電でサーバがダウンしたりすることもある

データを書き込んでいる途中でダウンした場合、書き込みが中途半端に終わる可能性もある

そういった際のリカバリーの仕組みを持っている

様々なDBMS (1)

リレーショナルDBMS (RDBMS)

MySQL, PostgreSQL, SQLite...

今日の講義では, この中のMySQLについて学びます

カラム指向DBMS

BigTable, Apache Cassandra, Apache HBase

様々なDBMS (2)

ドキュメント指向DBMS

MongoDB, Apache CouchDB, Elasticsearch

グラフDBMS

Neo4j

キーバリューストア (KVS)

Memcached, Redis

RDB・SQL・MySQL

ここからは具体的に

関係データベース(RDB)と関係モデル

RDBを扱うSQL

RDBのDBMSの1つであるMySQL

について説明します

関係データベース (RDB)

関係モデルに基づいたデータベース

RDBのDBMSのことをRDBMS(= リレーショナルDBMS)と言う

関係モデル

関係モデルとは?

データを関連として表現し、取り扱うモデル(=データの表現方法)のこと

ここでの「関係(リレーション)」とは?

関係において和, 差, 直積, 射影, 結合などの演算を数学的に定義できる

関係モデルの例

例: 戦国時代の武将の関係を表すモデル

属性 = ID, 名前, 生年月日

code:関係モデルの例

R: (ID, 名前, 生年月日) = {

(1, 織田信長, 1534-06-23),

(2, 徳川家康, 1543-01-31),

(3, 武田信玄, 1521-12-01),

(4, 上杉謙信, 1530-02-18)

}

(改めて) 関係モデルに基づいたデータベース

RDBMSにおけるテーブル・レコード・カラム

table:daimyoテーブル

id name birthday

1 織田信長 1534-06-23

2 徳川家康 1543-01-31

3 武田信玄 1521-12-01

4 上杉謙信 1530-02-18

table:servantテーブル

id daimyo_id name birthday

1 1 木下藤吉郎 1537-03-17

2 2 井伊直政 1561-03-04

SQLとは

関係データベースに問い合わせ(操作)を行うための言語

(何かの略語とかではない)

標準化されていて、ほとんどのRDBMSで共通の構文が利用可能

データの定義・操作 (CRUD操作)・制御が行える

テーブルを定義するSQL

code:sql

CREATE TABLE daimyo (

id INTEGER NOT NULL AUTO_INCREMENT,

name VARCHAR(32),

birthday DATE,

PRIMARY KEY(id)

);

code:sql

CREATE TABLE servant (

id INTEGER NOT NULL AUTO_INCREMENT,

daimyo_id INTEGER,

name VARCHAR(128),

birthday DATE,

PRIMARY KEY(id)

);

MySQL

リレーショナルDBMS(= RDBMS)の一種

オープンソースで開発されている

サポートはOracleが行っている

はてなでは、Mackerel以外のほぼ全てのサービスで使われている

MackerelではPostgreSQLを利用しています

以下ではMySQL特有の話が多くでてきます

後日自分で調べるときは、MySQL固有の話題に注意しつつ、また違いはどのようなものがあるか考えながら調べてみると良いかもしれません

MySQLにおけるデータ型

代表的なもの(よく使うもの)を紹介します

整数型

日付型

文字列型

整数型 (1)

右のほう程、大きい整数値を保存できる

桁あふれに気をつける必要がある

しかし, 21億レコードは案外簡単に到達できる

整数型 (2)

レコードを保存するとき, 値を指定しなければRDBMSが自動的に採番して値を割り当ててくれる

日付型

DATETIME

TIMESTAMP

文字列型 (1)

CHAR

固定長文字列, 0から255文字

VARCHAR

可変長文字列, 0から65,535バイト

文字列型 (2)

TEXT

最大65,535バイトk

MEDIUMTEXT

最長16,777,215バイト

LONGTEXT

最長4,294,967,295

MySQLにおける制約

制約とは

カラムに入るデータに対する制限

意図しないデータが入り込まなくて済む

MySQL 5.7には以下のような制約がある

データ型と制約を使ったテーブルの定義

code:sql

CREATE TABLE daimyo (

PRIMARY KEY (id),

UNIQUE KEY (name)

);

PRIMARY KEY制約

テーブル内でレコードを一意に識別できるカラム

1つのテーブルで1つ指定できる / 指定しなくてもよい

テーブル定義の削除

但し｢削除｣なので、テーブルに格納されたデータはすべて消えます

code:sql

-- daimyoテーブルの削除

DROP TABLE daimyo;

テーブル定義の変更

現状のテーブルと理想のテーブルの差分を記述して、それを適用するイメージ

code:sql

-- daimyoテーブルにruby(よみがな)テーブルを追加

ALTER TABLE daimyo ADD ruby VARCHAR(32);

レコードの操作(CRUD)

SQLを使って, データベースのデータを操作してみよう

CREATE(レコード作成)・READ(レコード読み込み)・UPDATE(レコード更新)・DELETE(レコード削除)の頭文字

code:元のテーブル

mysql> select * from daimyo;

+----+--------------+------------+

| id | name | birthday |

+----+--------------+------------+

| 1 | 織田信長 | 1534-06-23 |

| 2 | 徳川家康 | 1543-01-31 |

| 3 | 武田信玄 | 1521-12-01 |

| 4 | 上杉謙信 | 1530-02-18 |

+----+--------------+------------+

4 rows in set (0.00 sec)

mysql> select * from servant;

+----+-----------+-----------------+------------+

| id | daimyo_id | name | birthday |

+----+-----------+-----------------+------------+

| 1 | 1 | 木下藤吉郎 | 1534-06-23 |

| 2 | 2 | 井伊直政 | 1561-03-04 |

| 3 | 1 | 前田利家 | 1539-01-15 |

| 4 | 1 | 丹羽長秀 | 1535-10-16 |

| 5 | 2 | 本多忠勝 | 1548-03-17 |

| 6 | 2 | 榊原康政 | 1548-01-01 |

| 7 | 2 | 酒井忠次 | 1527-01-01 |

| 8 | 1 | 柴田勝家 | 1522-01-01 |

| 9 | 1 | 滝川一益 | 1525-01-01 |

| 10 | 2 | 石川数正 | 1533-01-01 |

| 11 | 3 | 真田昌幸 | 1547-01-01 |

| 12 | 4 | 直江景綱 | 1509-01-01 |

+----+-----------+-----------------+------------+

12 rows in set (0.00 sec)

mysql>

CREATE

レコードを追加する

code:sql

INSERT INTO daimyo (id, name, birthday) VALUES (5, '毛利元就', '1497-04-16');

INSERT INTO daimyo SET id = 5, name = '毛利元就', birthday = '1497-04-16';

READ

テーブルからレコードを検索する

code:sql

-- daimyoテーブルからIDが1のレコードを検索

SELECT * FROM daimyo WHERE id = 1;

-- daimyoテーブルからIDが1ではないレコードを検索

SELECT * FROM daimyo WHERE id != 1;

-- daimyoテーブルからbirthdayが1530年1月1日以降のレコードを検索

SELECT * FROM daimyo WHERE birthday >= '1530-01-01';

UPDATE

既に存在するレコードを更新する

code:sql

UPDATE servant SET name = '羽柴秀吉' WHERE id = 1;

DELETE

既に存在するレコードを削除する

code:sql

DELETE FROM daimyo WHERE id = 4;

WHERE節

比較

code:sql

-- daimyoテーブルからIDが1のレコードを検索

SELECT * FROM daimyo WHERE id = 1;

-- daimyoテーブルからIDが1ではないレコードを検索

SELECT * FROM daimyo WHERE id != 1;

-- daimyoテーブルからbirthdayが1530年1月1日以降のレコードを検索

SELECT * FROM daimyo WHERE birthday >= '1530-01-01';

code:sql

-- daimyoテーブルで, birthdayが1530年1月1日以降で,

-- nameが織田から始まるレコードを検索

SELECT * FROM daimyo WHERE birthday >= '1530-01-01' AND name LIKE '織田%';

-- daimyoテーブルで, idが1か2か3のレコードを検索

SELECT * FROM daimyo WHERE id = 1 OR id = 2 OR id = 3;

-- 上のSQLをINを使って表現

SELECT * FROM daimyo WHERE id IN (1, 2, 3);

ORDER BY句

検索結果をソートしたい時に使う

省略した場合は昇順となる

code:sql

- daimyoの全レコードを, birthdayの降順で検索

SELECT * FROM daimyo ORDER BY birthday DESC;

- daimyoの全レコードを, birthdayの昇順で検索

SELECT * FROM daimyo ORDER BY birthday ASC;

LIMIT句 / OFFSET句

code:sql

-- servantの全レコードから, birthdayの降順で検索して,

-- 5レコード読み飛ばした上で, 3件の検索結果を得る

SELECT * FROM servant ORDER BY birthday LIMIT 3 OFFSET 5;

mysql> SELECT * FROM servant ORDER BY birthday;

+----+-----------+-----------------+------------+

| id | daimyo_id | name | birthday |

+----+-----------+-----------------+------------+

| 12 | 4 | 直江景綱 | 1509-01-01 |

| 8 | 1 | 柴田勝家 | 1522-01-01 |

| 9 | 1 | 滝川一益 | 1525-01-01 |

| 7 | 2 | 酒井忠次 | 1527-01-01 |

| 10 | 2 | 石川数正 | 1533-01-01 |

| 1 | 1 | 木下藤吉郎 | 1534-06-23 |

| 4 | 1 | 丹羽長秀 | 1535-10-16 |

| 3 | 1 | 前田利家 | 1539-01-15 |

| 11 | 3 | 真田昌幸 | 1547-01-01 |

| 6 | 2 | 榊原康政 | 1548-01-01 |

| 5 | 2 | 本多忠勝 | 1548-03-17 |

| 2 | 2 | 井伊直政 | 1561-03-04 |

+----+-----------+-----------------+------------+

12 rows in set (0.00 sec)

mysql> SELECT * FROM servant ORDER BY birthday LIMIT 3 OFFSET 5;

+----+-----------+-----------------+------------+

| id | daimyo_id | name | birthday |

+----+-----------+-----------------+------------+

| 1 | 1 | 木下藤吉郎 | 1534-06-23 |

| 4 | 1 | 丹羽長秀 | 1535-10-16 |

| 3 | 1 | 前田利家 | 1539-01-15 |

+----+-----------+-----------------+------------+

3 rows in set (0.00 sec)

mysql>

GROUP BY

code:sql

-- servantテーブルから, daimyo_idごとのservant数を求める

SELECT daimyo_id, COUNT(*) AS servant_count FROM servant GROUP BY daimyo_id;

code:結果

mysql> SELECT daimyo_id, COUNT(*) AS servant_count FROM servant GROUP BY daimyo_id;

+-----------+---------------+

| daimyo_id | servant_count |

+-----------+---------------+

| 1 | 5 |

| 2 | 5 |

| 3 | 1 |

| 4 | 1 |

+-----------+---------------+

4 rows in set (0.00 sec)

mysql>

LEFT JOIN

code:sql

SELECT servant.name, daimyo.name

FROM servant LEFT JOIN daimyo ON servant.daimyo_id = daimyo.id;

code:結果

mysql> SELECT servant.name, daimyo.name

-> FROM servant LEFT JOIN daimyo ON servant.daimyo_id = daimyo.id;

+-----------------+--------------+

| name | name |

+-----------------+--------------+

| 木下藤吉郎 | 織田信長 |

| 井伊直政 | 徳川家康 |

| 前田利家 | 織田信長 |

| 丹羽長秀 | 織田信長 |

| 本多忠勝 | 徳川家康 |

| 榊原康政 | 徳川家康 |

| 酒井忠次 | 徳川家康 |

| 柴田勝家 | 織田信長 |

| 滝川一益 | 織田信長 |

| 石川数正 | 徳川家康 |

| 真田昌幸 | 武田信玄 |

| 直江景綱 | 上杉謙信 |

+-----------------+--------------+

12 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM servant LEFT JOIN daimyo ON servant.daimyo_id = daimyo.id;

+----+-----------+-----------------+------------+------+--------------+------------+

| id | daimyo_id | name | birthday | id | name | birthday |

+----+-----------+-----------------+------------+------+--------------+------------+

| 1 | 1 | 木下藤吉郎 | 1534-06-23 | 1 | 織田信長 | 1534-06-23 |

| 3 | 1 | 前田利家 | 1539-01-15 | 1 | 織田信長 | 1534-06-23 |

| 4 | 1 | 丹羽長秀 | 1535-10-16 | 1 | 織田信長 | 1534-06-23 |

| 8 | 1 | 柴田勝家 | 1522-01-01 | 1 | 織田信長 | 1534-06-23 |

| 9 | 1 | 滝川一益 | 1525-01-01 | 1 | 織田信長 | 1534-06-23 |

| 2 | 2 | 井伊直政 | 1561-03-04 | 2 | 徳川家康 | 1543-01-31 |

| 5 | 2 | 本多忠勝 | 1548-03-17 | 2 | 徳川家康 | 1543-01-31 |

| 6 | 2 | 榊原康政 | 1548-01-01 | 2 | 徳川家康 | 1543-01-31 |

| 7 | 2 | 酒井忠次 | 1527-01-01 | 2 | 徳川家康 | 1543-01-31 |

| 10 | 2 | 石川数正 | 1533-01-01 | 2 | 徳川家康 | 1543-01-31 |

| 11 | 3 | 真田昌幸 | 1547-01-01 | 3 | 武田信玄 | 1521-12-01 |

| 12 | 4 | 直江景綱 | 1509-01-01 | 4 | 上杉謙信 | 1530-02-18 |

+----+-----------+-----------------+------------+------+--------------+------------+

12 rows in set (0.03 sec)

mysql>

トランザクション・テーブル設計・パフォーマンス

トランザクション

トランザクションとは

ひとまとまりにして実行されるべき処理の集まり

これまで説明してきたSQLを組み合わせて、データに関する様々な処理が実現出来る

例: 銀行の送金

table:ユーザ

ID 名前 口座

2 Bさん 2000円

9 Sさん 0円

「BさんからSさんに1000円お金を送金する」ことを考える

ここでもし、(2)だけ失敗してしまったら? → Bさんの口座から1000円引かれただけで処理が終わってしまう……

「お金を送金する」という処理は、口座から引いて、足すという2つの操作を終えて初めて処理が完了したことになる

(1)と(2)の2つで1つの処理

ACID特性とは

トランザクションに求められる4つの性質のこと

これらの特性をDBMSは概ねサポートしている

All or Nothing

送金の例の場合、(2)に失敗した場合は(1)も適応されなくなる

例えばトランザクションの途中でUNIQUE KEY制約を満さなくなるとかが起こらないということ

一連の処理について, 外部から結果だけが見ることが出来て, 実行中の状態が他へ影響しないことを保証

様々な障害(停電, ネットワーク断, ストレージの故障, システムのクラッシュ, etc...)が起こっても復旧できるということ

テーブル設計の基本

テーブル間のリレーション

異なるテーブルの間で、レコードに関係性を持たせたいときがある

一対多の関係性

多対多の関係性

以下のテーブルで関連を持たせることを考えます

table:daimyoテーブル

id name

1 織田信長

2 徳川家康

3 武田信玄

4 上杉謙信

table:countryテーブル

id daimyo_id name

1 1 尾張

2 2 三河

3 3 信濃

4 3 甲斐

一対多

https://gyazo.com/22fbc66a43877c504c7c5157c2fc217c

多対多

https://gyazo.com/45d402570a40fff32c2b6af41f861e24

これによって, 多対多の関係性を示せる

table: daimyo_country_relation

daimyo_id country_id (補足)

1 1 織田ー尾張

2 2 徳川-三河

3 2 武田-三河

MySQLのパフォーマンス対策

ただし後半過程で実際にチームに配属されてから開発するときや、インターンが終わって自分でアプリケーションを作るとなった際は考慮する必要が出てくる

今の内から意識できるようにしておきましょう

データベースとパフォーマンス

データベースはWebサービスを開発/運営する上でパフォーマンスのボトルネックになりやすい

サービスが続く限りデータは増加し続ける

始めは実行時間が問題にならなかったクエリでも、データ量が増えたことで次第に実行に時間がかかってしまったり、、

放置したり、うまく対応出来ないとサービスの質の低下やサービスダウンにも繋がる！

クエリの実行に時間がかかる

DBの負荷が高まり、サービス全体のパフォーマンスが落ちる→ ユーザに返すレスポンスが遅くなる → ユーザが離れる！

こうならないためのパフォーマンス対策

インデックス

EXPLAIN

N+1や遅いクエリに気をつける

etc...

インデックスとは

インデックス(索引)とは、テーブルの行を高速に検索するためのDBMSの仕組み

MySQLではデータを検索する際、インデックスが無いと先頭行から始めてテーブル全体を読み取る

検索対象のカラムにインデックスが作成されている場合、データを全て調べる必要はなく効率的にデータの検索が出来る

MySQLではB木(B-Tree)というデータ構造が使われる

インデックスの作り方

明示的に作る

制約により自動で作られる

PRIMARY KEY

UNIQUE KEY

例

birthdayカラムについてインデックスを作成している

code:index_example.sql

-- birthdayカラムについてインデックスを作成

CREATE TABLE daimyo (

PRIMARY KEY (id),

UNIQUE KEY (name),

KEY (birthday) -- INDEX birthday_index (birthday)

);

SELECT * FROM daimyo WHERE birthday '1530-01-01' <= birthday && birthday <= '1530-12-31'

インデックス注意点

インデックスを張ると、レコードの更新・削除時にインデックスも更新するので、オーバーヘッドがある

インデックスを構築した分だけ、テーブルの容量が増える

全部のカラムにインデックスを作成したら良いというわけでもない

インデックスが使われないケースもある

インデックスを活用できるSQLになっていない

例えば全件探索の方が早いとか

検索対象のテーブルの行数が少ない

etc...

EXPLAIN

インデックスを使って効率的にデータを検索できることは紹介した

では、効率的なクエリを実行していないことは、どうやって判断すればよい……？

EXPLAINとは

MySQLで、クエリの実行計画(= 使用するインデックスやテーブルを結合する順序など)を出力する方法

code:explain.sql

EXPLAIN SELECT servant.name, daimyo.name

FROM servant LEFT JOIN daimyo ON servant.daimyo_id = daimyo.id;

code:EXPLAIN実行結果(テーブル表示だと見にくなったので、\Gを付けて縦表示にしました)

mysql> select count(*) from daimyo;

+----------+

| count(*) |

+----------+

| 4 |

+----------+

1 row in set (0.00 sec)

mysql> select count(*) from servant;

+----------+

| count(*) |

+----------+

| 12 |

+----------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT servant.name, daimyo.name FROM servant LEFT JOIN daimyo ON servant.daimyo_id = daimyo.id\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: servant

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 12

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: daimyo

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: intern_sample.servant.daimyo_id

rows: 1

Extra: NULL

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

格言

パフォーマンス対策をするときは勘で対処してはいけない

きちんと計測し、ボトルネックを把握して対処していく必要がある

N + 1問題 、他

多くの場合クエリを実行するサーバはWebアプリを実行しているサーバと別 → クエリ実行に多少の時間がかかる

クエリの実行数に気をつける

code:N+1_before.go

// 例えば bookmark の配列から user 一覧を取得したいと思ったとき

bookmarks, _ := FindAllBookmarks() // bookmarks の一覧を取得するので 1 回

users := make([]User, len(bookmarks))

for i, bookmark := range bookmarks {

user, _ := FindUserByBookmarkID(bookmark.ID) // DBアクセスをするメソッドが配列の長さ(N)回実行される

}

これは、以下のように2回のクエリに書き直せる

code:N+1_after.go

bookmarks, _ := FindAllBookmarks() // bookmarks の一覧を取得するので 1 回

bookmark_ids := ... // bookmark.ID の配列用意

users, _ := FindUsersByBookmarkIDs(bookmark_ids) // bookmark.IDの配列を指定してuser一覧を取得するように

不要なクエリは投げない

遅くなりがちなクエリに気をつける

インデックスを使っていない/うまく活用できていないクエリ

複雑なJOIN、サブクエリ

今日の講義で話さなかったこと

時間の都合上, 以下の内容については割愛しました

サブクエリ

DISTINCT

トリガー

DBMSそのものの構築やユーザー管理, 権限など

etc...

課題

課題1(必須): テーブル設計

要件

ここまで出来たら、一度メンターにテーブル設計とスキーマをレビューしてもらってください

オススメテク

テーブル設計の時は図を書いてみるのも良いでしょう

ここまで正確なものを作る必要は無いですが、テーブルの関連は図にしてみるとわかりやすくなることが多いです

課題2(必須): Intern-Diaryの機能実装

以下の機能を実装してみましょう

想定されるエンドポイント

少なくとも, 以下のようなエンドポイントが必要になるでしょう:

機能実装の進め方

一気に実装せず、1機能ごとに作業を分離して進めていくのがオススメです

例えば次のように

テンプレートを作成して, ブラウザで見れるようにする

(余裕があれば) テストを書いてみる

自分は見た目から作った方がテンションが上がる！という方はモデル層→Web層→レポジトリ/サービス層という順に、サーバーを立ち上げた時に目に見えるところから作っていくのも良いかもしれません

レポジトリ層のコードを書くときはいきなりGoのコードを書くのではなく、実行したいSQLを書いて練習してからコードを書き始めるのがオススメです

オプション課題

追加で機能実装をしてみましょう。例えば...

ページング機能

こういうやつです

はてなブックマークの例

https://gyazo.com/5f15fcb54e36066df31edb91e260e2d8

記事を削除する機能

などなど